package com.css.fxfzypg.modules.auxiliaryDecisionManage.auxiliaryDecision.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.auxiliaryDecisionManage.auxiliaryDecision.entity.AuxiliaryDecision;
import com.css.fxfzypg.modules.auxiliaryDecisionManage.auxiliaryDecision.entity.AuxiliaryDecisionReport;
import com.css.fxfzypg.modules.auxiliaryDecisionManage.auxiliaryDecision.param.AuxiliaryDecisionParam;
import com.css.fxfzypg.modules.auxiliaryDecisionManage.auxiliaryDecision.repository.AuxiliaryDecisionRepository;
import com.css.fxfzypg.modules.auxiliaryDecisionManage.auxiliaryDecision.vo.AuxiliaryDecisionVO;
import com.css.fxfzypg.modules.baseData.entity.DisasterScene;
import com.css.fxfzypg.modules.baseData.entity.ErcmEntity;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/03/23
 * @Description: 震后辅助决策管理
 */
@Repository
public class AuxiliaryDecisionRepositoryImpl implements AuxiliaryDecisionRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public String getDataByCataId(String cata_id) {
        StringBuilder sql = new StringBuilder("select id from ypg_auxiliary_decision where cata_id = '"+ cata_id +"'");
        try {
            String id = jdbcTemplate.queryForObject(sql.toString(), String.class);
            return id;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    //根据省市县查询灾害情景
    @Override
    public DisasterScene getDisasterScenario(String province, String city, String prefecture) {
        String sql = "select * from sys_disasterscene where ds_province = '"+province+"' and ds_city= '"+city+"' and ds_counties ='"+prefecture+"' and ds_status = '"+ YNEnum.Y+"'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DisasterScene.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //根据省市县查询应急处理对策
    @Override
    public ErcmEntity getEmergencyDisaster(String province, String city, String prefecture) {
        String sql = "select * from data_ercm where ercm_province = '"+province+"' and ercm_city= '"+city+"' and ercm_prefecture ='"+prefecture+"' and ercm_isdelete = '"+ YNEnum.Y+"'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ErcmEntity.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //批量新增
    @Override
    public void batchSave(ArrayList<AuxiliaryDecision> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO ypg_auxiliary_decision (id,cata_id,earthquake_time,magnitude," +
                "longitude,latitude,province,city,prefecture,building_damage,casualties," +
                "geologic_hazard,secondary_disaster,earthquake_traffic,other_disaster,rfasi," +
                "edcai,tss_aer,ass_aer,pss_aer,tmir,climate_cr,national_cr,tdwrapo,olftdw_cr,source_depth,location)" +
                "values (:id,:cataId,:earthquakeTime,:magnitude," +
                ":longitude,:latitude,:province,:city,:prefecture,:buildingDamage,:casualties," +
                ":geologicHazard,:secondaryDisaster,:earthquakeTraffic,:otherDisaster,:rfasi," +
                ":edcai,:tssAer,:assAer,:pssAer,:tmir,:climateCr,:nationalCr,:tdwrapo,:olftdwCr,:sourceDepth,:location)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public Map<String, Object> getPage(int curPage, int pageSize, AuxiliaryDecisionParam param) {
        StringBuilder sql = new StringBuilder("select id,earthquake_time,magnitude,province,city,prefecture,cata_id from ypg_auxiliary_decision ");
        StringBuilder whereSql = new StringBuilder(" where 1 = 1");
        StringBuilder orderSql = new StringBuilder(" order by earthquake_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        //时间范围
        if (!PlatformObjectUtils.isEmpty(param.getStartTime()) & !PlatformObjectUtils.isEmpty(param.getEndTime())) {
            whereSql.append("and earthquake_time between '" + param.getStartTime() + "' and '" + param.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(param.getStartTime()) & PlatformObjectUtils.isEmpty(param.getEndTime())) {
            whereSql.append("and earthquake_time  >='" + param.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(param.getEndTime()) & PlatformObjectUtils.isEmpty(param.getStartTime())) {
            whereSql.append("and earthquake_time  <='" + param.getEndTime() + "'");
        }
        //震级
        if (!PlatformObjectUtils.isEmpty(param.getMagnitude())) {
            whereSql.append(" and magnitude = '" + param.getMagnitude() + "'");
        }
        //省名称
        if (!PlatformObjectUtils.isEmpty(param.getProvince())) {
            whereSql.append(" and province = '" + param.getProvince() + "'");
        }
        //市名称
        if (!PlatformObjectUtils.isEmpty(param.getCity())) {
            whereSql.append(" and city = '" + param.getCity() + "'");
        }
        //县名称
        if (!PlatformObjectUtils.isEmpty(param.getPrefecture())) {
            whereSql.append(" and prefecture = '" + param.getPrefecture() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(AuxiliaryDecisionVO.class)).size();
        List<AuxiliaryDecisionVO> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(AuxiliaryDecisionVO.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }
    //灾害情景
    @Override
    public void updateErcmEntityById(AuxiliaryDecision param) {
        String sql = "update ypg_auxiliary_decision "+
                " set building_damage = :buildingDamage,casualties=:casualties, " +
                "geologic_hazard = :geologicHazard,secondary_disaster = :secondaryDisaster, " +
                "earthquake_traffic = :earthquakeTraffic,other_disaster = :otherDisaster " +
                " where  id = '" + param.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //应急处理对策
    @Override
    public void updateDisasterSceneById(AuxiliaryDecision param) {
        String sql = "update ypg_auxiliary_decision "+
                " set rfasi = :rfasi,edcai=:edcai, " +
                "tss_aer = :tssAer,ass_aer = :assAer, " +
                "pss_aer = :pssAer,tmir = :tmir, " +
                "climate_cr = :climateCr,national_cr = :nationalCr,"+
                "tdwrapo = :tdwrapo,olftdw_cr = :olftdwCr "+
                " where  id = '" + param.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //根据id查询
    @Override
    public AuxiliaryDecision findById(String id) {
        String sql ="select * from ypg_auxiliary_decision where id = '"+id+"'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(AuxiliaryDecision.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void updateReportIdById(String reportId, String id) {
        String sql = "update ypg_auxiliary_decision  set report_id = '"+reportId+"' where  id = '" + id + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void saveAuxiliaryDecisionReport(AuxiliaryDecisionReport report) {
        String sql="insert into ypg_auxiliary_decision_report values(:id, :reportPath, :reportCreateUserId, :reportCreateTime, :reportName, :reportFactName,:attachId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(report);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public AuxiliaryDecisionReport getReportById(String reportId) {
        String sql ="select * from ypg_auxiliary_decision_report where id = '"+reportId+"'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(AuxiliaryDecisionReport.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public BigDecimal getDistance(String longitude, String latitude, BigDecimal dhpEpicenterLongitude, BigDecimal dhpEpicenterLatitude) {
        String sql = "SELECT st_length(st_transform(ST_ShortestLine(st_geometryfromtext('POINT(' || ? || ' ' || ? || ')',4490),st_geometryfromtext('POINT(' || ? || ' ' || ? || ')',4490)), 4527))";

        return jdbcTemplate.queryForObject(sql, BigDecimal.class, dhpEpicenterLongitude,dhpEpicenterLatitude, longitude, latitude);
    }

}
